﻿using System;
using System.Data;
using Agile.DataAccess.Providers.Common;
using Agile.DataAccess.Providers.Common.Builders;
using System.Text;

namespace Agile.DataAccess
{
	internal class SqliteProvider : IDbProvider
	{
		public string ProviderName
		{ 
			get
			{
				return "System.Data.SQLite";
			} 
		}

		public bool SupportsOutputParameters
		{
			get { return true; }
		}

		public bool SupportsMultipleResultsets
		{
			get { return true; }
		}

		public bool SupportsMultipleQueries
		{
			get { return true; }
		}

		public bool SupportsStoredProcedures
		{
			get { return false; }
		}

		public bool RequiresIdentityColumn
		{
			get { return false; }
		}

		public IDbConnection CreateConnection(string connectionString)
		{
			return ConnectionFactory.CreateConnection(ProviderName, connectionString);
		}

		public string GetParameterName(string parameterName)
		{
			return "@" + parameterName;
		}

		public string GetSelectBuilderAlias(string name, string alias)
		{
			return name + " as " + alias;
		}

		public string GetSqlForQueryBuilder(QueryBuilderData data)
		{
			var sql = "";
			sql = "SELECT " + data.Select;
			sql += " FROM " + data.From;
			if (data.WhereSql.Length > 0)
				sql += " WHERE " + data.WhereSql;
			if (data.GroupBy.Length > 0)
				sql += " GROUP BY " + data.GroupBy;
			if (data.Having.Length > 0)
				sql += " HAVING " + data.Having;
            if (data.Orderby.Length > 0)
                sql += " ORDER BY " + data.Orderby;
			if (data.PagingItemsPerPage > 0 && data.PagingCurrentPage > 0)
				sql += string.Format(" LIMIT {0}, {1}", data.GetFromItems() - 1, data.GetToItems());		
			return sql;
		}

        public string GetSqlForSelectBuilder(SelectBuilderData data, bool isBuilder)
        {
            var sql = new StringBuilder();
            sql.Append("SELECT ");
            var temp = string.Empty;
            foreach (var column in data.Columns)
            {
                if (temp.Length > 0)
                    temp += ",";
                temp += this.EscapeColumnName(column.ColumnName);
            }
            sql.Append(isBuilder ? temp : " *");
            sql.Append("\r\nFROM ");
            sql.Append(data.ObjectName);

            temp = string.Empty;
            foreach (var column in data.Where)
            {
                if (temp.Length > 0)
                    temp += " AND ";
                temp += string.Format("{0} = {1}{2}", this.EscapeColumnName(column.ColumnName), "@", column.ParameterName);
            }
            if (temp.Length > 0)
                sql.Append("\r\nWHERE " + temp);

            temp = string.Empty;
            foreach (var column in data.OrderBy)
            {
                if (temp.Length > 0)
                    temp += ",";
                temp += string.Format("{0} {1}", this.EscapeColumnName(column.ColumnName), column.Value);
            }
            if (temp.Length > 0)
                sql.Append("\r\nORDER BY " + temp);
            if (data.PagingItemsPerPage > 0 && data.PagingCurrentPage > 0)
                sql.Append(string.Format(" LIMIT {0}, {1}", data.GetFromItems() - 1, data.GetToItems()));	
            return sql.ToString();
        }

		public string GetSqlForInsertBuilder(BuilderData data)
		{
			return new InsertBuilderSqlGenerator().GenerateSql(this, "@", data);
		}

		public string GetSqlForUpdateBuilder(BuilderData data)
		{
			return new UpdateBuilderSqlGenerator().GenerateSql(this, "@", data);
		}

		public string GetSqlForDeleteBuilder(BuilderData data)
		{
			return new DeleteBuilderSqlGenerator().GenerateSql(this, "@", data);
		}

        public string GetSqlForExistsBuilder(BuilderData data)
        {
            return new ExistsBuilderSqlGenerator().GenerateSql(this, "@", data, "SELECT EXISTS (SELECT 1 FROM {0} WHERE {1})");
        }

		public string GetSqlForStoredProcedureBuilder(BuilderData data)
		{
			throw new NotImplementedException();
		}

        public string GetSqlForPagingBuilder(SelectBuilderData data, string orderBy)
        {
            var sql = new StringBuilder();
            sql.Append(data.ObjectName);
            if (!string.IsNullOrEmpty(orderBy))
                sql.Append("\r\nORDER BY " + orderBy + "\r\n");
            if (data.PagingItemsPerPage > 0 && data.PagingCurrentPage > 0)
                sql.Append(string.Format(" LIMIT {0}, {1}", data.GetFromItems() - 1, data.GetToItems()));
            return sql.ToString();
        }

		public DataTypes GetDbTypeForClrType(Type clrType)
		{
			return new DbTypeMapper().GetDbTypeForClrType(clrType);
		}

		public object ExecuteReturnLastId<T>(IDbCommand command, string identityColumnName = null)
		{
			if(command.Data.Sql[command.Data.Sql.Length - 1] != ';')
				command.Script(";");

			command.Script("SELECT last_insert_rowid();");

			object lastId = null;
			
			command.Data.ExecuteQueryHandler.ExecuteQuery(false, () =>
			{
				lastId = command.Data.InnerCommand.ExecuteScalar();
			});

			return lastId;
		}

		public void OnCommandExecuting(IDbCommand command)
		{
		}

		public string EscapeColumnName(string name)
		{
			if (name.Contains("["))
				return name;
			return "[" + name + "]";
		}
	}
}
